using AuthService.Domain.Entities;
using AuthService.Domain.Repositories;
using AuthService.Infrastructure.DTOs;
using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Npgsql;
using System.Data;

namespace AuthService.Infrastructure.Repositories;

/// <summary>
/// 用户仓储实现类
/// 使用Dapper进行数据访问，支持PostgreSQL数据库
/// </summary>
public partial class UserRepository : BaseRepository<User, Guid>, IUserRepository
{

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="configuration">配置</param>
    /// <param name="logger">日志记录器</param>
    public UserRepository(IConfiguration configuration, ILogger<UserRepository> logger)
        : base(configuration, logger)
    {
    }

    /// <summary>
    /// 获取表名
    /// </summary>
    protected override string GetTableName()
    {
        return "\"Users\"";
    }



    /// <summary>
    /// 根据ID获取用户
    /// </summary>
    public async Task<User?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            SELECT * FROM ""Users""
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var userDto = await connection.QueryFirstOrDefaultAsync<UserDto>(sql, new { Id = id });
            return userDto?.ToEntity();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取用户失败，ID: {UserId}", id);
            throw;
        }
    }

    /// <summary>
    /// 根据用户名获取用户
    /// </summary>
    public async Task<User?> GetByUsernameAsync(string username, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            SELECT * FROM ""Users""
            WHERE ""Username"" = @Username AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var userDto = await connection.QueryFirstOrDefaultAsync<UserDto>(sql, new { Username = username });
            return userDto?.ToEntity();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据用户名获取用户失败，用户名: {Username}", username);
            throw;
        }
    }

    /// <summary>
    /// 根据邮箱获取用户
    /// </summary>
    public async Task<User?> GetByEmailAsync(string email, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            SELECT * FROM ""Users""
            WHERE ""Email"" = @Email AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var userDto = await connection.QueryFirstOrDefaultAsync<UserDto>(sql, new { Email = email });
            return userDto?.ToEntity();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据邮箱获取用户失败，邮箱: {Email}", email);
            throw;
        }
    }

    /// <summary>
    /// 检查用户名是否已存在
    /// </summary>
    public async Task<bool> ExistsByUsernameAsync(string username, Guid? excludeUserId = null, CancellationToken cancellationToken = default)
    {
        var sql = "SELECT COUNT(1) FROM \"Users\" WHERE \"Username\" = @Username AND \"IsDeleted\" = false";
        object parameters;

        if (excludeUserId.HasValue)
        {
            sql += " AND \"Id\" != @ExcludeUserId";
            parameters = new { Username = username, ExcludeUserId = excludeUserId.Value };
        }
        else
        {
            parameters = new { Username = username };
        }

        try
        {
            using var connection = CreateConnection();
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "检查用户名是否存在失败，用户名: {Username}", username);
            throw;
        }
    }

    /// <summary>
    /// 检查邮箱是否已存在
    /// </summary>
    public async Task<bool> ExistsByEmailAsync(string email, Guid? excludeUserId = null, CancellationToken cancellationToken = default)
    {
        var sql = "SELECT COUNT(1) FROM \"Users\" WHERE \"Email\" = @Email AND \"IsDeleted\" = false";
        object parameters;

        if (excludeUserId.HasValue)
        {
            sql += " AND \"Id\" != @ExcludeUserId";
            parameters = new { Email = email, ExcludeUserId = excludeUserId.Value };
        }
        else
        {
            parameters = new { Email = email };
        }

        try
        {
            using var connection = CreateConnection();
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "检查邮箱是否存在失败，邮箱: {Email}", email);
            throw;
        }
    }

    /// <summary>
    /// 获取用户列表（分页）
    /// </summary>
    public async Task<(IEnumerable<User> Users, int TotalCount)> GetPagedAsync(
        int pageNumber = 1,
        int pageSize = 20,
        string? searchTerm = null,
        string? tenantId = null,
        bool? isActive = null,
        CancellationToken cancellationToken = default)
    {
        var whereConditions = new List<string> { "\"IsDeleted\" = false" };
        var parameters = new DynamicParameters();

        if (!string.IsNullOrWhiteSpace(searchTerm))
        {
            whereConditions.Add("(\"Username\" ILIKE @SearchTerm OR \"Email\" ILIKE @SearchTerm OR \"DisplayName\" ILIKE @SearchTerm)");
            parameters.Add("SearchTerm", $"%{searchTerm}%");
        }

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            whereConditions.Add("\"TenantId\" = @TenantId");
            parameters.Add("TenantId", tenantId);
        }

        if (isActive.HasValue)
        {
            whereConditions.Add("\"IsActive\" = @IsActive");
            parameters.Add("IsActive", isActive.Value);
        }

        var whereClause = string.Join(" AND ", whereConditions);
        var offset = (pageNumber - 1) * pageSize;

        var countSql = $"SELECT COUNT(1) FROM \"Users\" WHERE {whereClause}";
        var dataSql = $@"
            SELECT * FROM ""Users""
            WHERE {whereClause}
            ORDER BY ""CreatedAt"" DESC
            LIMIT @PageSize OFFSET @Offset";

        parameters.Add("PageSize", pageSize);
        parameters.Add("Offset", offset);

        try
        {
            using var connection = CreateConnection();

            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            var userDtos = await connection.QueryAsync<UserDto>(dataSql, parameters);
            var users = userDtos.Select(dto => dto.ToEntity());

            return (users, totalCount);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取分页用户列表失败");
            throw;
        }
    }

    /// <summary>
    /// 根据角色获取用户列表
    /// </summary>
    public async Task<IEnumerable<User>> GetByRoleAsync(string role, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""Users""
            WHERE ""IsDeleted"" = false
            AND ""IsActive"" = true
            AND ""Roles""::jsonb ? @Role";

        object parameters;

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND \"TenantId\" = @TenantId";
            parameters = new { Role = role, TenantId = tenantId };
        }
        else
        {
            parameters = new { Role = role };
        }

        try
        {
            using var connection = CreateConnection();
            var userDtos = await connection.QueryAsync<UserDto>(sql, parameters);
            return userDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据角色获取用户列表失败，角色: {Role}", role);
            throw;
        }
    }

    /// <summary>
    /// 根据权限获取用户列表
    /// </summary>
    public async Task<IEnumerable<User>> GetByPermissionAsync(string permission, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""Users""
            WHERE ""IsDeleted"" = false
            AND ""IsActive"" = true
            AND ""Permissions""::jsonb ? @Permission";

        object parameters;

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND \"TenantId\" = @TenantId";
            parameters = new { Permission = permission, TenantId = tenantId };
        }
        else
        {
            parameters = new { Permission = permission };
        }

        try
        {
            using var connection = CreateConnection();
            var userDtos = await connection.QueryAsync<UserDto>(sql, parameters);
            return userDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据权限获取用户列表失败，权限: {Permission}", permission);
            throw;
        }
    }

    /// <summary>
    /// 创建用户
    /// </summary>
    public async Task<User> CreateAsync(User user, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            INSERT INTO ""Users"" (
                ""Id"", ""Username"", ""Email"", ""PasswordHash"", ""PasswordSalt"", ""DisplayName"",
                ""CreatedAt"", ""UpdatedAt"", ""CreatedBy"", ""UpdatedBy"", ""IsDeleted"", ""DeletedAt"", ""DeletedBy""
            ) VALUES (
                @Id, @Username, @Email, @PasswordHash, @PasswordSalt, @DisplayName,
                @CreatedAt, @UpdatedAt, @CreatedBy, @UpdatedBy, @IsDeleted, @DeletedAt, @DeletedBy
            )";

        try
        {
            using var connection = CreateConnection();
            var userDto = UserDto.FromEntity(user);
            await connection.ExecuteAsync(sql, userDto);

            _logger.LogInformation("用户创建成功，ID: {UserId}, 用户名: {Username}", user.Id, user.Username);
            return user;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "创建用户失败，用户名: {Username}", user.Username);
            throw;
        }
    }

    /// <summary>
    /// 更新用户
    /// </summary>
    public async Task<User> UpdateAsync(User user, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            UPDATE ""Users"" SET
                ""Username"" = @Username, ""Email"" = @Email, ""PasswordHash"" = @PasswordHash, ""PasswordSalt"" = @PasswordSalt,
                ""DisplayName"" = @DisplayName, ""UpdatedAt"" = @UpdatedAt, ""UpdatedBy"" = @UpdatedBy
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var userDto = UserDto.FromEntity(user);
            var affectedRows = await connection.ExecuteAsync(sql, userDto);

            if (affectedRows == 0)
                throw new InvalidOperationException($"用户不存在或已被删除，ID: {user.Id}");

            _logger.LogInformation("用户更新成功，ID: {UserId}, 用户名: {Username}", user.Id, user.Username);
            return user;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "更新用户失败，ID: {UserId}", user.Id);
            throw;
        }
    }

    /// <summary>
    /// 删除用户（软删除）
    /// </summary>
    public async Task<bool> DeleteAsync(Guid id, string? deletedBy = null, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            UPDATE ""Users"" SET
                ""IsDeleted"" = true,
                ""DeletedAt"" = @DeletedAt,
                ""DeletedBy"" = @DeletedBy,
                ""UpdatedAt"" = @UpdatedAt,
                ""UpdatedBy"" = @UpdatedBy
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var now = DateTime.UtcNow;
            var affectedRows = await connection.ExecuteAsync(sql, new
            {
                Id = id,
                DeletedAt = now,
                DeletedBy = deletedBy,
                UpdatedAt = now,
                UpdatedBy = deletedBy
            });

            var success = affectedRows > 0;
            if (success)
            {
                _logger.LogInformation("用户删除成功，ID: {UserId}", id);
            }
            else
            {
                _logger.LogWarning("用户删除失败，用户不存在或已被删除，ID: {UserId}", id);
            }

            return success;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "删除用户失败，ID: {UserId}", id);
            throw;
        }
    }

    /// <summary>
    /// 物理删除用户
    /// </summary>
    public async Task<bool> HardDeleteAsync(Guid id, CancellationToken cancellationToken = default)
    {
        const string sql = "DELETE FROM \"Users\" WHERE \"Id\" = @Id";

        try
        {
            using var connection = CreateConnection();
            var affectedRows = await connection.ExecuteAsync(sql, new { Id = id });

            var success = affectedRows > 0;
            if (success)
            {
                _logger.LogInformation("用户物理删除成功，ID: {UserId}", id);
            }
            else
            {
                _logger.LogWarning("用户物理删除失败，用户不存在，ID: {UserId}", id);
            }

            return success;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "物理删除用户失败，ID: {UserId}", id);
            throw;
        }
    }

    /// <summary>
    /// 批量创建用户
    /// </summary>
    public async Task<IEnumerable<User>> CreateBatchAsync(IEnumerable<User> users, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            INSERT INTO Users (
                Id, Username, Email, PasswordHash, PasswordSalt, DisplayName, AvatarUrl, PhoneNumber,
                IsEmailVerified, IsPhoneVerified, IsActive, IsLocked, LockoutEnd, FailedLoginAttempts,
                LastLoginAt, LastLoginIp, Roles, Permissions, Metadata, TenantId,
                CreatedAt, UpdatedAt, CreatedBy, UpdatedBy, IsDeleted, DeletedAt, DeletedBy
            ) VALUES (
                @Id, @Username, @Email, @PasswordHash, @PasswordSalt, @DisplayName, @AvatarUrl, @PhoneNumber,
                @IsEmailVerified, @IsPhoneVerified, @IsActive, @IsLocked, @LockoutEnd, @FailedLoginAttempts,
                @LastLoginAt, @LastLoginIp, @Roles, @Permissions, @Metadata, @TenantId,
                @CreatedAt, @UpdatedAt, @CreatedBy, @UpdatedBy, @IsDeleted, @DeletedAt, @DeletedBy
            )";

        try
        {
            using var connection = CreateConnection();
            var userDtos = users.Select(UserDto.FromEntity);
            await connection.ExecuteAsync(sql, userDtos);

            _logger.LogInformation("批量创建用户成功，数量: {Count}", users.Count());
            return users;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "批量创建用户失败");
            throw;
        }
    }

    /// <summary>
    /// 批量更新用户
    /// </summary>
    public async Task<IEnumerable<User>> UpdateBatchAsync(IEnumerable<User> users, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            UPDATE ""Users"" SET
                ""Username"" = @Username, ""Email"" = @Email, ""PasswordHash"" = @PasswordHash, ""PasswordSalt"" = @PasswordSalt,
                ""DisplayName"" = @DisplayName, ""AvatarUrl"" = @AvatarUrl, ""PhoneNumber"" = @PhoneNumber,
                ""IsEmailVerified"" = @IsEmailVerified, ""IsPhoneVerified"" = @IsPhoneVerified, ""IsActive"" = @IsActive,
                ""IsLocked"" = @IsLocked, ""LockoutEnd"" = @LockoutEnd, ""FailedLoginAttempts"" = @FailedLoginAttempts,
                ""LastLoginAt"" = @LastLoginAt, ""LastLoginIp"" = @LastLoginIp, ""Roles"" = @Roles, ""Permissions"" = @Permissions,
                ""Metadata"" = @Metadata, ""TenantId"" = @TenantId, ""UpdatedAt"" = @UpdatedAt, ""UpdatedBy"" = @UpdatedBy
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var userDtos = users.Select(UserDto.FromEntity);
            await connection.ExecuteAsync(sql, userDtos);

            _logger.LogInformation("批量更新用户成功，数量: {Count}", users.Count());
            return users;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "批量更新用户失败");
            throw;
        }
    }

    /// <summary>
    /// 搜索用户
    /// </summary>
    public async Task<(IEnumerable<User> Users, int TotalCount)> SearchAsync(
        string keyword,
        int page = 1,
        int size = 10,
        string? tenantId = null,
        CancellationToken cancellationToken = default)
    {
        const string searchSql = @"
            SELECT * FROM ""Users""
            WHERE ""IsDeleted"" = false
            AND (
                ""Username"" ILIKE @Keyword OR
                ""Email"" ILIKE @Keyword OR
                ""DisplayName"" ILIKE @Keyword
            )
            AND (@TenantId IS NULL OR ""TenantId"" = @TenantId)
            ORDER BY ""CreatedAt"" DESC
            LIMIT @Size OFFSET @Offset";

        const string countSql = @"
            SELECT COUNT(*)
            FROM ""Users""
            WHERE ""IsDeleted"" = false
            AND (
                ""Username"" ILIKE @Keyword OR
                ""Email"" ILIKE @Keyword OR
                ""DisplayName"" ILIKE @Keyword
            )
            AND (@TenantId IS NULL OR ""TenantId"" = @TenantId)";

        try
        {
            using var connection = CreateConnection();
            var searchPattern = $"%{keyword}%";
            var offset = (page - 1) * size;

            var parameters = new
            {
                Keyword = searchPattern,
                TenantId = tenantId,
                Size = size,
                Offset = offset
            };

            var users = await connection.QueryAsync<User>(searchSql, parameters);
            var totalCount = await connection.QuerySingleAsync<int>(countSql, new { Keyword = searchPattern, TenantId = tenantId });

            _logger.LogDebug("搜索用户完成，关键词: {Keyword}, 返回 {Count} 条记录，总计 {Total} 条",
                keyword, users.Count(), totalCount);

            return (users, totalCount);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "搜索用户失败，关键词: {Keyword}", keyword);
            throw;
        }
    }
}
